#include "sql.h"

const Sqlservconfig sqlservconfig;

mysql::mysql()
{
    this->dbserverip = sqlservconfig.dbserverip;
    this->dbusername = sqlservconfig.dbusername;
    this->dbpassword = sqlservconfig.dbpassword;
    this->dbname     = sqlservconfig.dbname    ;
    
    this->conn=nullptr;
    this->res=nullptr;
    this->row=0;

};


void mysql::connect() {
    cout<<"sql: connect exe\n";
    conn = mysql_init(NULL);
    //if (!mysql_real_connect(conn, "127.0.0.1", "mainuser", "Bu1.buqi", "userdb", 0, NULL, 0)) {
    if (!mysql_real_connect(conn, dbserverip.c_str(), dbusername.c_str(), dbpassword.c_str(), dbname.c_str(), 0, NULL, 0)) {
    std::cerr << "sql: Error connecting to database: " << mysql_error(conn) << std::endl;
    mysql_close(conn);
    return ;
    }

};

void mysql::insert() {
    connect();

    cout<<"sql: insert exe\n";
    string sqlcmdline = string("INSERT INTO ")+table+key+string(" VALUES ")+value;
    // 插入数据
    //if (mysql_query(conn, "INSERT INTO users (username, email) VALUES ('Xiao hong', 'hong@example.com')")) {
    if (mysql_query(conn, sqlcmdline.c_str())) {
        std::cerr << "sql: Error inserting data: " << mysql_error(conn) << std::endl;
        mysql_close(conn);
        return ;
    }

};
void mysql::remove() {
    connect();

    cout<<"sql: remove exe\n";
    string sqlcmdline = string("DELETE FROM ")+table+string(" WHERE ")+wherekey+" = "+wherevalue;
    // 删除数据
    //if (mysql_query(conn, "DELETE FROM users WHERE username = 'John Doe'")) {
    if (mysql_query(conn, sqlcmdline.c_str())) {
        std::cerr << "sql: Error deleting data: " << mysql_error(conn) << std::endl;
        mysql_close(conn);
        return ;
    }

};

void mysql::select() {
    connect();

    cout<<"sql: select exe\n";
    string sqlcmdline = string("SELECT * FROM ")+table+string(" WHERE ")+wherekey+" = "+wherevalue ;
    // 查询数据
    //if (mysql_query(conn, "SELECT * FROM users")) {
    if (mysql_query(conn, sqlcmdline.c_str())) {
        std::cerr << "sql: Error querying data: " << mysql_error(conn) << std::endl;
        mysql_close(conn);
        return ;
    }
    res = mysql_store_result(conn);
    displayres();
};

void mysql::update() {
    connect();

    cout<<"sql: update exe\n";
    string sqlcmdline = string("UPDATE ")+ table +" SET "+key+" = "+value +string(" WHERE ") + wherekey+string(" = ")+wherevalue;
    // 查询数据
    //if (mysql_query(conn, "UPDATE users SET age = 30 WHERE username = 'john_doe'")) {
    if (mysql_query(conn, sqlcmdline.c_str())) {
        std::cerr << "sql: Error querying data: " << mysql_error(conn) << std::endl;
        mysql_close(conn);
        return ;
    }
};


void mysql::upsert() {

    connect();

    cout<<"sql: upsert exe\n";
    string sqlcmdline = string("INSERT INTO ")+table+string(" ")+key +string(" VALUES ") + value+ " ON DUPLICATE KEY UPDATE "+ wherekey+ " = VALUES "+ wherevalue;
    //string sqlcmdline= string("INSERT INTO  users  username VALUES aa ON DUPLICATE KEY UPDATE username VALUES aa");
    //string sqlcmdline = string("INSERT INTO ")+ table +" "+key+ " VALUES "+ value +" ON DUPLICATE KEY UPDATE";
    
    // 查询数据
    //if (mysql_query(conn, "UPSERT INTO table_name (字段1, 字段2，……) VALUES (字段1值, 字段2值, ……);")) {
    if (mysql_query(conn, sqlcmdline.c_str())) {
        std::cerr << "sql: Error querying data: " << mysql_error(conn) << std::endl;
        mysql_close(conn);
        return ;
    }

};



void mysql::displayres() {
    string dataString("");
    if (res == nullptr) {
        std::cerr << "sql: Result Error: " << mysql_error(conn) << std::endl;
        mysql_free_result(res);
        mysql_close(conn);
        return ;
    }

    // 遍历结果集
    while ((row = mysql_fetch_row(res))) {
        // 检查每一列的数据长度
        unsigned long *lengths = mysql_fetch_lengths(res);
        // 将结果添加到字符串中
        for (unsigned int i = 0; i < mysql_num_fields(res); ++i) {
            if (row[i] != nullptr) {
                dataString += std::string(row[i], lengths[i]); // 使用长度来避免尾随空字符
                dataString += ", "; // 分隔不同列
            } else {
                dataString += "NULL, "; // 处理空值的情况
            }
        }
        dataString.pop_back(); // 删除最后一个逗号
        dataString.pop_back(); // 删除最后一个空格
        dataString += "\n"; // 添加换行符来区分不同的记录

    }

    // 输出字符串中的数据
    std::cout << "sql: The data stored in the string is:\n" << dataString;

};
